<?php

namespace App\model;

use Illuminate\Database\Eloquent\Model;
use DB;
class Student extends Model
{

     protected $table='student';
     public $timestamps=false;
    /*
     * 作者：*****
     * 内容：查询所有在校学员
     */
    public function selectOnline()
    {
        return $this
            ->select('stu_id','use_name','use_num','use_sex','use_card','use_phone','pap_name','sub_name','use_enty','ust_state')
            ->join('user','user.use_id','=','student.stu_id')
            ->join('papers','papers.pap_id','=','student.per_id')
            ->join('subject','subject.sub_id','=','student.suj_id')
            ->join('use_stu','use_stu.ust_id','=','student.stu_state')
            ->paginate(2)
            ->toArray();
    }

    /*
     * 查询所有学员的信息
     * 作者：*****
     */

    public function getstudent($id)
    {
        return $this
                    ->select('stu_id','stu_endtime','stu_time','use_sex','use_phone','use_name','use_num','use_card','pap_name','sub_name','use_enty','ust_state')
                    ->join('papers','papers.pap_id', '=', 'student.per_id')
                    ->join('use_stu','use_stu.ust_id', '=', 'student.stu_state')
                    ->join('subject','subject.sub_id', '=','student.suj_id')
                    ->join('user','user.use_id', '=', 'student.stu_id')
                    ->where('student.use_id',$id)
                    ->get();  
    }

    /*
     * 作者：*****
     * 内容：根据搜索条件查询学员信息
     */
    public function selectStudentInfo($search)
    {
        return $this
            ->select('stu_id','use_num','use_name','use_sex','use_card','use_phone','pap_name','sub_name','use_enty','ust_state')
            ->join('user','user.use_id','=','student.stu_id')
            ->join('papers','papers.pap_id','=','student.per_id')
            ->join('subject','subject.sub_id','=','student.suj_id')
            ->join('use_stu','use_stu.ust_id','=','student.stu_state')
            ->where($search)
            ->paginate(2)
            ->toArray();
    }
     
    /*
     * 作者：*****
     * 内容：根据教练ID搜索自己的学员信息
     */

    public function selectIdInfo($search,$id)
    {
        $sql="select stu_id,use_num,use_name,stu_time,stu_endtime,use_sex,use_card,use_phone,pap_name,sub_name,use_enty,ust_state from student INNER JOIN user on user.use_id=student.stu_id INNER JOIN papers on papers.pap_id=student.per_id INNER JOIN subject on subject.sub_id=student.suj_id INNER JOIN use_stu on use_stu.ust_id=student.stu_state where student.use_id=".$id;
        
        foreach($search as $k=>$v)
        {
            if(!empty($v))
            {
                if($k=='use_num'||$k=='use_name')
                {
                    $sql.=" and ".$k."='".trim($v)."'";
                }
                else
                {
                    $sql.=" and student.".$k."='".trim($v)."'";
                }
            }
        }
        
        return DB::select($sql);
    }

    /*
     * 作者：*****
     * 内容：查询所有在校学员
     */
    public function selectOneStudent($id)
    {
        return $this
            ->select('stu_id','use_name','use_num','use_sex','use_card','use_phone','per_id','suj_id','use_enty','stu_state')
            ->join('user','user.use_id','=','student.stu_id')
            ->where('stu_id',$id)
            ->first()
            ->toArray();
    }
    /*
     * 作者：*****
     * 内容；根据ID修改学员的关联信息
     */
    public function updateStuInfo($id,$stu_data)
    {
        return $this
            ->where('stu_id',$id)
            ->update($stu_data);
    }

    /*
     * 科目一
     * 作者：*****
     */

    public function one()
    {
        return $this
                    ->select('stu_desc','stu_entrytime')
                    ->join('subject', 'subject.sub_id', '=', 'student.suj_id' )
                    ->where('subject.sub_id',1)
                    ->get();
    }

    /*
     * 科目二
     * 作者：*****
     */

    public function two()
    {
        return $this
                    ->select('stu_desc','stu_entrytime')
                    ->join('subject', 'subject.sub_id', '=', 'student.suj_id' )
                    ->where('subject.sub_id',2)
                    ->get();
    }

    /*
     * 科目三
     * 作者：*****
     */

    public function three()
    {
        return $this
                    ->select('stu_desc','stu_entrytime')
                    ->join('subject', 'subject.sub_id', '=', 'student.suj_id' )
                    ->where('subject.sub_id',3)
                    ->get();
    }

    /*
     * 科目四
     * 作者：*****
     */

    public function four()
    {
        return $this
                    ->select('stu_desc','stu_entrytime')
                    ->join('subject', 'subject.sub_id', '=', 'student.suj_id' )
                    ->where('subject.sub_id',4)
                    ->get();
    }

    /*
     * 报名
     * 作者：*****
     */

    public function five()
    {
        return $this
                    ->select('stu_desc','stu_entrytime')
                    ->join('subject', 'subject.sub_id', '=', 'student.suj_id' )
                    ->where('subject.sub_id',5)
                    ->get();
    }

    /*
     * 考试进度
     * 作者：*****
     */

    public function when()
    {
        return $this
                    ->select('subject.sub_id')
                    ->join('subject', 'subject.sub_id', '=', 'student.suj_id' )
                    ->first();
    }


}

